//
//  HRStore.m
//  APJ Resource
//
//  Created by Richard Zhao on 1/23/14.
//  Copyright (c) 2014 APJ. All rights reserved.
//

#import "HRStore.h"
#import "Const.h"
#import "Staff.h"
#import "User.h"
#import "StaffSkillMatrix.h"
#import "EducationBackground.h"

@implementation HRStore

//实现单例模式
+ (HRStore *)sharedStore
{
    static HRStore *sharedStore = nil;
    if (!sharedStore) {
        sharedStore = [[super allocWithZone:nil] init];
    }
    return sharedStore;
}

+ (id)allocWithZone:(NSZone *)zone
{
    return [self sharedStore];
}

// 数据库文件保存路径
- (NSString *)dbStorePath
{
    NSArray *documentDirectories = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *documentDirector = [documentDirectories objectAtIndex:0];
    return [documentDirector stringByAppendingPathComponent:DB_FILE_NAME];
}

// init 初始化 NSManagedObjectModel 和 NSPersistentStoreCoordinator
- (id)init
{
    self = [super init];
    if (self) {
        model = [NSManagedObjectModel mergedModelFromBundles:nil];
        NSPersistentStoreCoordinator *psc = [[NSPersistentStoreCoordinator alloc] initWithManagedObjectModel:model];
        NSString *path = [self dbStorePath];
        NSURL *storeURL = [NSURL fileURLWithPath:path];
        NSError *error = nil;
        
        if (![psc addPersistentStoreWithType:NSSQLiteStoreType
                              configuration:nil URL:storeURL
                                    options:@{NSMigratePersistentStoresAutomaticallyOption:@YES, NSInferMappingModelAutomaticallyOption:@YES} 
                                       error:&error]) {
            [NSException raise:@"open db failed" format:@"Reason: %@", [error localizedDescription]];
        }
        
        context = [[NSManagedObjectContext alloc] init];
        [context setPersistentStoreCoordinator:psc];
        [context setUndoManager:nil];
    }
    
    return self;
}

- (BOOL)syncTable:(NSString *)tableName withData:(NSArray *)datas byKeys:(NSArray *)keys
{
    NSError *error = nil;
    
    for (NSDictionary *data in datas) {
        if ([[data allKeys] count] == 0) {
            continue;
        }
        NSPredicate *p = [self buildPredicateFromData:data byKey:keys];
        NSFetchRequest *request = [[NSFetchRequest alloc] init];
        NSEntityDescription *e = [[model entitiesByName] objectForKey:tableName];
        [request setEntity:e];
        [request setPredicate:p];
        NSError *error = nil;
        NSArray *result = [context executeFetchRequest:request error:&error];
        if (!result) {
            [NSException raise:@"fetch failed" format:@"reason: %@", [error localizedDescription]];
        }
        
        id r = nil;
        
        if ([result count] > 0) {
            r = [result objectAtIndex:0];
        } else {
            r = [NSEntityDescription insertNewObjectForEntityForName:tableName inManagedObjectContext:context];
            if (!r) {
                [NSException raise:@"sycn failed" format:@"insert %@ error",tableName];
            }
        }
        
        [self copyDataFrom:data toObject:r];
    }
    
    BOOL success = [context save:&error];
    if (!success) {
        NSLog(@"sync %@ failed: %@", tableName, [error localizedDescription]);
    }
    
    return YES;
}

// 把从 json 中解析的一个对象（键值对）的各个属性值拷贝到 NSManagedObject
- (void)copyDataFrom:(NSDictionary *)dict toObject:(NSManagedObject *)obj
{
    for (NSString *key in [dict allKeys]) {
        // 如果是一个 int 类型的外键，而且是空值，就不要设置值。
        if ([key hasSuffix:@"Id"] && [[dict objectForKey:key] isEqual:@""]) {
            //[obj setValue:nil forKey:key];
        } else {
            [obj setValue:[dict objectForKey:key] forKey:key];
        }
    }
}

// 根据传入的主键数组和json数据内容构建出到CoreData中查找数据的查询条件
- (NSPredicate *)buildPredicateFromData:(NSDictionary *)dict byKey:(NSArray *)keys
{
    NSString *keyValue = nil;
    NSMutableString *sql = [NSMutableString stringWithString:@"1=1"];
    for (NSString *key in keys) {
        if ([key hasSuffix:@"Code"]) {
            keyValue = [NSString stringWithFormat:@"'%@'", [dict objectForKey:key]];
        } else {
            keyValue = [dict objectForKey:key];
        }
        
        int length = [key length];
        NSRange first, rest;
        first.location = 0;
        first.length = 1;
        rest.location = 1;
        rest.length = length - 1;
        
        NSString *firstStr = [[key substringWithRange:first] lowercaseString];
        NSString *restStr = [key substringWithRange:rest];
        
        NSString *attribute = [NSString stringWithFormat:@"%@%@", firstStr, restStr];
        
        [sql appendFormat:@" AND %@=%@", attribute, keyValue];
    }
    NSPredicate *p = [NSPredicate predicateWithFormat:sql];
    
    return p;
}

- (User *)findUserById:(NSString *)uid
{
    NSPredicate *p = [NSPredicate predicateWithFormat:@"userAccount=%@", uid];
    NSFetchRequest *request = [[NSFetchRequest alloc] init];
    NSEntityDescription *e = [[model entitiesByName] objectForKey:@"User"];
    [request setEntity:e];
    [request setPredicate:p];
    
    NSError *error = nil;
    NSArray *result = [context executeFetchRequest:request error:&error];
    if (!result) {
        [NSException raise:@"find user failed" format:@"reason: %@", [error localizedDescription]];
    }
    if ([result count] < 1) {
        return nil;
    } else {
        return [result objectAtIndex:0];
    }
}

- (NSArray *)allDept
{
    NSFetchRequest *request = [[NSFetchRequest alloc] init];
    NSEntityDescription *e = [[model entitiesByName] objectForKey:@"Dept"];
    [request setEntity:e];
    
    NSError *error = nil;
    NSArray *result = [context executeFetchRequest:request error:&error];
    if (!result) {
        [NSException raise:@"get all dept failed" format:@"reason: %@", [error localizedDescription]];
    }
    if ([result count] < 1) {
        return nil;
    } else {
        return result;
    }	
}

- (NSArray *)allSkillCategory
{
    NSFetchRequest *request = [[NSFetchRequest alloc] init];
    NSEntityDescription *e = [[model entitiesByName] objectForKey:@"SkillCategory"];
    [request setEntity:e];
    
    NSError *error = nil;
    NSArray *result = [context executeFetchRequest:request error:&error];
    if (!result) {
        [NSException raise:@"get all skill category failed" format:@"reason: %@", [error localizedDescription]];
    }
    if ([result count] < 1) {
        return nil;
    } else {
        return result;
    }
}

- (NSArray *)skillUnderCategory:(NSString *)skillCategory
{
    NSPredicate *p = [NSPredicate predicateWithFormat:@"skillCategoryCode=%@", skillCategory];
    NSFetchRequest *request = [[NSFetchRequest alloc] init];
    NSEntityDescription *e = [[model entitiesByName] objectForKey:@"Skill"];
    [request setEntity:e];
    [request setPredicate:p];
    
    NSError *error = nil;
    NSArray *result = [context executeFetchRequest:request error:&error];
    return result;
}

- (NSArray *)allSkillsOfStaff:(int)staffId
{
    NSPredicate *p = [NSPredicate predicateWithFormat:@"staffId=%d", staffId];
    NSFetchRequest *request = [[NSFetchRequest alloc] init];
    NSEntityDescription *e = [[model entitiesByName] objectForKey:@"StaffSkillMatrix"];
    [request setEntity:e];
    [request setPredicate:p];
    
    NSError *error = nil;
    NSArray *result = [context executeFetchRequest:request error:&error];
    return result;
}

- (NSString *)allSkillsStringOfStaff:(int)staffId
{
    NSMutableString *skills = [[NSMutableString alloc] init];
    NSArray *matrixs = [self allSkillsOfStaff:staffId];
    for (StaffSkillMatrix *m in matrixs) {
        if (![skills isEqual:@""]) {
            [skills appendString:@";    "];
        }
        [skills appendString:[m skillCode]];
    }
    
    return skills;
}

- (NSArray *)educationOfStaff:(int)staffId
{
    NSPredicate *p = [NSPredicate predicateWithFormat:@"staffId=%d", staffId];
    NSFetchRequest *request = [[NSFetchRequest alloc] init];
    NSEntityDescription *e = [[model entitiesByName] objectForKey:@"EducationBackground"];
    [request setEntity:e];
    [request setPredicate:p];
    
    NSError *error = nil;
    NSArray *result = [context executeFetchRequest:request error:&error];
    return result;
}

- (NSArray *)workExperienceOfStaff:(int)staffId
{
    NSPredicate *p = [NSPredicate predicateWithFormat:@"staffId=%d", staffId];
    NSFetchRequest *request = [[NSFetchRequest alloc] init];
    NSEntityDescription *e = [[model entitiesByName] objectForKey:@"WorkExperience"];
    [request setEntity:e];
    [request setPredicate:p];
    
    NSError *error = nil;
    NSArray *result = [context executeFetchRequest:request error:&error];
    return result;
}

- (NSArray *)qualificationOfStaff:(int)staffId
{
    NSPredicate *p = [NSPredicate predicateWithFormat:@"staffId=%d", staffId];
    NSFetchRequest *request = [[NSFetchRequest alloc] init];
    NSEntityDescription *e = [[model entitiesByName] objectForKey:@"Qualification"];
    [request setEntity:e];
    [request setPredicate:p];
    
    NSError *error = nil;
    NSArray *result = [context executeFetchRequest:request error:&error];
    return result;
}

- (NSArray *)skillsOfStaff:(int)staffId
{
    NSPredicate *p = [NSPredicate predicateWithFormat:@"staffId=%d", staffId];
    NSFetchRequest *request = [[NSFetchRequest alloc] init];
    NSEntityDescription *e = [[model entitiesByName] objectForKey:@"StaffSkillMatrix"];
    [request setEntity:e];
    [request setPredicate:p];
    
    NSError *error = nil;
    NSArray *result = [context executeFetchRequest:request error:&error];
    return result;
}

- (NSArray *)projectAssignToStaff:(int)staffId
{
    NSPredicate *p = [NSPredicate predicateWithFormat:@"staffId=%d", staffId];
    NSFetchRequest *request = [[NSFetchRequest alloc] init];
    NSEntityDescription *e = [[model entitiesByName] objectForKey:@"ProjectAllocation"];
    [request setEntity:e];
    [request setPredicate:p];
    
    NSError *error = nil;
    NSArray *result = [context executeFetchRequest:request error:&error];
    return result;
}

- (NSArray *)projectAssignFroProject:(int)projectId
{
    NSPredicate *p = [NSPredicate predicateWithFormat:@"projectId=%d", projectId];
    NSFetchRequest *request = [[NSFetchRequest alloc] init];
    NSEntityDescription *e = [[model entitiesByName] objectForKey:@"ProjectAllocation"];
    [request setEntity:e];
    [request setPredicate:p];
    
    NSError *error = nil;
    NSArray *result = [context executeFetchRequest:request error:&error];
    return result;
}

- (Dept *)findDeptById:(int)deptId
{
    NSPredicate *p = [NSPredicate predicateWithFormat:@"deptId=%d", deptId];
    NSFetchRequest *request = [[NSFetchRequest alloc] init];
    NSEntityDescription *e = [[model entitiesByName] objectForKey:@"Dept"];
    [request setEntity:e];
    [request setPredicate:p];
    
    NSError *error = nil;
    NSArray *result = [context executeFetchRequest:request error:&error];
    return [result objectAtIndex:0];
}

- (Project *)findProjectById:(int)projectId
{
    NSPredicate *p = [NSPredicate predicateWithFormat:@"projectId=%d", projectId];
    NSFetchRequest *request = [[NSFetchRequest alloc] init];
    NSEntityDescription *e = [[model entitiesByName] objectForKey:@"Project"];
    [request setEntity:e];
    [request setPredicate:p];
    
    NSError *error = nil;
    NSArray *result = [context executeFetchRequest:request error:&error];
    return [result objectAtIndex:0];
}

- (Staff *)findStaffById:(int)staffId
{
    NSPredicate *p = [NSPredicate predicateWithFormat:@"staffId=%d", staffId];
    NSFetchRequest *request = [[NSFetchRequest alloc] init];
    NSEntityDescription *e = [[model entitiesByName] objectForKey:@"Staff"];
    [request setEntity:e];
    [request setPredicate:p];
    
    NSError *error = nil;
    NSArray *result = [context executeFetchRequest:request error:&error];
    return [result objectAtIndex:0];
}

- (NSArray *)findStaffAboutName:(NSString *)name inDept:(int)dept withSkills:(NSArray *)skills
{
    NSMutableString *query = [[NSMutableString alloc] init];
    NSString *n = [NSString stringWithFormat:@"*%@*", name];
    [query appendFormat:@"(englishName like[cd] $NAME || chineseName like[cd] $NAME)"];
    if (dept != 0) {
        [query appendFormat:@" && deptId == $DEPT"];
    }
    
    NSPredicate *p = [NSPredicate predicateWithFormat:query];
    NSDictionary *dict = [NSDictionary dictionaryWithObjectsAndKeys:n, @"NAME",[NSNumber numberWithInt:dept], @"DEPT", nil];
    NSPredicate *t = [p predicateWithSubstitutionVariables:dict];
    
    NSFetchRequest *request = [[NSFetchRequest alloc] init];
    NSEntityDescription *e = [[model entitiesByName] objectForKey:@"Staff"];
    [request setEntity:e];
    [request setPredicate:t];
    
    NSError *error = nil;
    NSArray *staffs = [context executeFetchRequest:request error:&error];
    
    NSMutableArray *result = [NSMutableArray array];
    e = [[model entitiesByName] objectForKey:@"StaffSkillMatrix"];
    [request setEntity:e];
    int matched;
    for (Staff *staff in staffs) {
        p = [NSPredicate predicateWithFormat:@"staffId == %d", [staff staffId]];
        [request setPredicate:p];
        NSArray *matrix = [context executeFetchRequest:request error:&error];
        matched = 0;
        for (NSString *skill in skills) {
            p = [NSPredicate predicateWithFormat:@"skillCode = %@", skill];
            if ([matrix filteredArrayUsingPredicate:p].count > 0)
            {
                matched ++;
            }
        }
        
        if (matched == skills.count)
        {
            [result addObject:staff];
        }
    }
    staffs = nil;
    return result;
}

- (NSArray *)findProjectAboutName:(NSString *)name inDept:(int)dept
{
    NSMutableString *query = [[NSMutableString alloc] init];
    NSString *n = [NSString stringWithFormat:@"*%@*", name];
    [query appendFormat:@"(projectShortName like[cd] $NAME || projectFullName like[cd] $NAME)"];
    if (dept != 0) {
        [query appendFormat:@" && deptId == $DEPT"];
    }
    
    NSPredicate *p = [NSPredicate predicateWithFormat:query];
    NSDictionary *dict = [NSDictionary dictionaryWithObjectsAndKeys:n, @"NAME",[NSNumber numberWithInt:dept], @"DEPT", nil];
    NSPredicate *t = [p predicateWithSubstitutionVariables:dict];
    
    NSFetchRequest *request = [[NSFetchRequest alloc] init];
    NSEntityDescription *e = [[model entitiesByName] objectForKey:@"Project"];
    [request setEntity:e];
    [request setPredicate:t];
    
    NSError *error = nil;
    NSArray *result = [context executeFetchRequest:request error:&error];
    return result;
}

- (NSArray *)reqByDept:(int)deptId inMonth:(NSTimeInterval)theMonth
{
    NSTimeInterval startDate=0;
    NSTimeInterval endDate=0;
    NSDate *sDate;
    NSDate *eDate;
       
    NSDateComponents* cmpnts = [[NSCalendar currentCalendar] components:(NSYearCalendarUnit|NSMonthCalendarUnit|NSDayCalendarUnit | NSHourCalendarUnit | NSMinuteCalendarUnit | NSSecondCalendarUnit) fromDate:[NSDate dateWithTimeIntervalSince1970:theMonth]];
    cmpnts.hour = 0;
    cmpnts.minute = 0;
    cmpnts.second = 0.0;
    cmpnts.day = 1;
    startDate = [[[NSCalendar currentCalendar] dateFromComponents:cmpnts] timeIntervalSince1970];
    sDate = [NSDate dateWithTimeIntervalSince1970:(startDate+NSTimeIntervalSince1970)];
    
    cmpnts.month += 1;
    endDate = [[[NSCalendar currentCalendar] dateFromComponents:cmpnts] timeIntervalSince1970];
    eDate = [NSDate dateWithTimeIntervalSince1970:(endDate+NSTimeIntervalSince1970)];
    
    NSMutableString *query = [[NSMutableString alloc] init];
    [query appendFormat:@"reqArriveDate >= $START && reqArriveDate < $END"];
    if (deptId != 0) {
        [query appendFormat:@" && deptId == $DEPT"];
    }
    
    NSPredicate *p = [NSPredicate predicateWithFormat:query];
    NSDictionary *dict =
    [NSDictionary dictionaryWithObjectsAndKeys:sDate, @"START", eDate, @"END",[NSNumber numberWithInt:deptId], @"DEPT", nil];
    NSPredicate *t = [p predicateWithSubstitutionVariables:dict];
    
    NSFetchRequest *request = [[NSFetchRequest alloc] init];
    NSEntityDescription *e = [[model entitiesByName] objectForKey:@"ResourceRequirement"];
    [request setEntity:e];
    [request setPredicate:t];
    
    NSError *error = nil;
    NSArray *result = [context executeFetchRequest:request error:&error];
    return result;
}

@end
